import json
import os
import sys
from pathlib import Path

# 添加项目根目录到Python路径
project_root = Path(__file__).parent.parent
sys.path.insert(0, str(project_root))

import pymysql
from sqlalchemy import create_engine, text
from config.mysql_config import MYSQL_CONFIG
from typing import List, Dict, Any

# 导入日志系统
from utils.log_initializer import init_logging_system
from utils.log_manager import get_database_logger, get_system_logger

# 初始化日志系统
init_logging_system()
db_logger = get_database_logger('init_mysql_db')
system_logger = get_system_logger('init_mysql_db')


def create_mysql_connection():
    """创建MySQL数据库连接"""
    mysql_config = MYSQL_CONFIG
    
    # 构建MySQL连接字符串
    connection_string = f"mysql+pymysql://{mysql_config['user']}:{mysql_config['password']}@{mysql_config['host']}:{mysql_config['port']}/{mysql_config['database']}?charset={mysql_config['charset']}"
    
    try:
        # 创建数据库引擎
        engine = create_engine(connection_string)
        
        # 测试连接
        with engine.connect() as connection:
            result = connection.execute(text("SELECT 1"))
            db_logger.info("MySQL数据库连接成功")
            
        return engine
    except Exception as e:
        db_logger.error(f"MySQL数据库连接失败: {e}")
        return None


def convert_sqlite_type_to_mysql(sqlite_type: str) -> str:
    """将SQLite数据类型转换为MySQL数据类型"""
    type_mapping = {
        'TEXT': 'VARCHAR(255)',
        'INTEGER': 'INT',
        'REAL': 'DECIMAL(10,2)',
        'BLOB': 'BLOB',
        'NUMERIC': 'DECIMAL(10,2)',
        'VARCHAR': 'VARCHAR(255)',
        'CHAR': 'CHAR(50)',
        'DATETIME': 'DATETIME',
        'DATE': 'DATE',
        'TIME': 'TIME'
    }
    
    # 处理带长度的类型，如VARCHAR(100)
    if '(' in sqlite_type:
        return sqlite_type
    
    return type_mapping.get(sqlite_type.upper(), 'VARCHAR(255)')


def load_json_data(file_path: str) -> Dict[str, Any]:
    """加载JSON数据文件"""
    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            data = json.load(f)
        return data
    except Exception as e:
        system_logger.error(f"读取文件 {file_path} 失败: {e}")
        return None


def create_table_and_insert_data(engine, json_data: Dict[str, Any], table_name: str = None):
    """根据JSON数据创建表并插入数据"""
    if not json_data:
        return False
    
    # 获取表信息
    table_name = table_name or json_data.get('table_name', 'unknown_table')
    columns = json_data.get('column_names', [])
    types = json_data.get('column_types', [])
    rows = json_data.get('sample_rows', [])
    
    if not columns or not types:
        db_logger.warning(f"表 {table_name} 缺少列信息，跳过")
        return False
    
    try:
        with engine.connect() as connection:
            # 删除已存在的表
            drop_sql = f"DROP TABLE IF EXISTS {table_name}"
            connection.execute(text(drop_sql))
            db_logger.info(f"已删除旧表: {table_name}")
            
            # 创建表SQL
            mysql_types = [convert_sqlite_type_to_mysql(t) for t in types]
            columns_def = []
            
            # 只为第一个包含'id'的字段设置主键
            primary_key_set = False
            for col, col_type in zip(columns, mysql_types):
                # 为第一个主键字段添加特殊处理
                if not primary_key_set and 'id' in col.lower() and col_type == 'VARCHAR(255)':
                    columns_def.append(f"`{col}` {col_type} PRIMARY KEY")
                    primary_key_set = True
                else:
                    columns_def.append(f"`{col}` {col_type}")
            
            create_table_sql = f"CREATE TABLE `{table_name}` (\n"
            create_table_sql += ",\n".join([f"    {col_def}" for col_def in columns_def])
            create_table_sql += "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
            
            # 执行建表
            connection.execute(text(create_table_sql))
            db_logger.info(f"创建表成功: {table_name}")
            
            # 插入数据
            if rows:
                # 构建插入SQL
                placeholders = ', '.join([':' + col for col in columns])
                insert_sql = f"INSERT INTO `{table_name}` ({', '.join(['`' + col + '`' for col in columns])}) VALUES ({placeholders})"
                
                # 批量插入数据
                insert_count = 0
                for row in rows:
                    try:
                        # 准备数据，确保所有列都有值
                        row_data = {}
                        for col in columns:
                            value = row.get(col, None)
                            # 处理空值
                            if value == '' or value is None:
                                row_data[col] = None
                            else:
                                row_data[col] = value
                        
                        connection.execute(text(insert_sql), row_data)
                        insert_count += 1
                    except Exception as row_error:
                        db_logger.warning(f"插入行数据失败: {row_error}")
                        continue
                
                # 提交事务
                connection.commit()
                db_logger.info(f"表 {table_name} 插入 {insert_count}/{len(rows)} 条记录")
            else:
                db_logger.warning(f"表 {table_name} 没有示例数据")
            
            return True
            
    except Exception as e:
        db_logger.error(f"处理表 {table_name} 失败: {e}")
        return False


def import_all_data(data_dir: str = 'data', clear_existing: bool = True):
    """导入所有JSON数据文件到MySQL"""
    system_logger.info("开始MySQL数据导入")
    
    # 创建数据库连接
    engine = create_mysql_connection()
    if not engine:
        system_logger.error("无法连接到MySQL数据库，导入终止")
        return False
    
    # 获取所有JSON文件
    json_files = []
    if os.path.exists(data_dir):
        for file in os.listdir(data_dir):
            if file.endswith('.json'):
                json_files.append(os.path.join(data_dir, file))
    
    if not json_files:
        system_logger.error(f"在目录 {data_dir} 中没有找到JSON文件")
        return False
    
    system_logger.info(f"找到 {len(json_files)} 个JSON数据文件")
    
    # 处理每个JSON文件
    success_count = 0
    total_count = len(json_files)
    
    for i, json_file in enumerate(json_files, 1):
        system_logger.info(f"[{i}/{total_count}] 处理文件: {json_file}")
        
        # 加载JSON数据
        json_data = load_json_data(json_file)
        if json_data:
            # 创建表并插入数据
            if create_table_and_insert_data(engine, json_data):
                success_count += 1
            else:
                system_logger.error(f"处理文件 {json_file} 失败")
        else:
            system_logger.error(f"加载文件 {json_file} 失败")
    
    # 显示总结
    system_logger.info("导入完成统计")
    system_logger.info(f"成功: {success_count}/{total_count} 个文件")
    system_logger.info(f"失败: {total_count - success_count}/{total_count} 个文件")
    
    return success_count == total_count


def show_database_info():
    """显示数据库信息"""
    engine = create_mysql_connection()
    if not engine:
        return
    
    try:
        with engine.connect() as connection:
            # 获取所有表
            tables_result = connection.execute(text("SHOW TABLES"))
            tables = [row[0] for row in tables_result]
            
            db_logger.info("数据库表信息")
            
            for table in tables:
                # 获取表记录数
                count_result = connection.execute(text(f"SELECT COUNT(*) FROM `{table}`"))
                count = count_result.fetchone()[0]
                db_logger.info(f"{table}: {count} 条记录")
            
    except Exception as e:
        db_logger.error(f"获取数据库信息失败: {e}")


if __name__ == '__main__':
    print("🔧 MySQL数据导入工具")
    print("=" * 50)
    system_logger.info("MySQL数据导入工具启动")
    
    # 检查命令行参数
    if len(sys.argv) > 1:
        if sys.argv[1] == '--info':
            system_logger.info("显示数据库信息模式")
            show_database_info()
            sys.exit(0)
        elif sys.argv[1] == '--help':
            print("使用方法:")
            print("  python init_mysql_db.py          # 导入所有数据")
            print("  python init_mysql_db.py --info   # 显示数据库信息")
            print("  python init_mysql_db.py --help   # 显示帮助")
            system_logger.info("显示帮助信息")
            sys.exit(0)
    
    # 执行数据导入
    system_logger.info("开始执行数据导入")
    success = import_all_data()
    
    if success:
        print("\n🎉 所有数据导入成功!")
        system_logger.info("所有数据导入成功")
        show_database_info()
    else:
        print("\n⚠️ 部分数据导入失败，请检查错误信息")
        system_logger.error("部分数据导入失败")
        sys.exit(1)